﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for CarInsuranceDB
/// </summary>
/// 
namespace CE.CarInsurance
{
    public class CarInsuranceDB
    {
        public CarInsuranceDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(CarInsurance carinsurance)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocCarInsuranceInsertUpdateSingleItem";

            SortedList _sPara = new SortedList();

            #region Parameter

            _sPara.Add("@CarInsId", carinsurance.CarInsId);
            _sPara.Add("@CIVariableId", carinsurance.CIVariableId);
            _sPara.Add("@ProviderCompanyId", carinsurance.ProviderCompanyId);
            _sPara.Add("@CXCoverTypeId", carinsurance.CXCoverTypeId);
            _sPara.Add("@PackageId", carinsurance.PackageId);
            _sPara.Add("@PolicyName", carinsurance.PolicyName);
            _sPara.Add("@TypeofPlan", carinsurance.TypeofPlan);

            _sPara.Add("@CarAge", carinsurance.CarAge);
            _sPara.Add("@SumInsured", carinsurance.SumInsured);
            _sPara.Add("@ThirdPartyBodilyPerPerson", carinsurance.ThirdPartyBodilyPerPerson);
            _sPara.Add("@ThirdPartyBodilyPerAccident", carinsurance.ThirdPartyBodilyPerAccident);
            _sPara.Add("@ThirdPartyPropertyDamage", carinsurance.ThirdPartyPropertyDamage);
            _sPara.Add("@FireAndTheft", carinsurance.FireAndTheft);
            _sPara.Add("@BailBond", carinsurance.BailBond);

            _sPara.Add("@AnnualPremium", carinsurance.AnnualPremium);            
            _sPara.Add("@ProposerExcess", carinsurance.ProposerExcess);
            _sPara.Add("@isPersonalAccident", carinsurance.isPersonalAccident);           
            _sPara.Add("@PolicyHolderAmt", carinsurance.PolicyHolderAmt);
            _sPara.Add("@EachPassengerAmt", carinsurance.EachPassengerAmt);
            _sPara.Add("@PersonalAccAccumulated", carinsurance.PersonalAccAccumulated);
            _sPara.Add("@isMedicalExpense", carinsurance.isMedicalExpense);
            _sPara.Add("@MedicalExpense", carinsurance.MedicalExpense);
            _sPara.Add("@isCashAllowance", carinsurance.isCashAllowance);
            _sPara.Add("@CashAllowance", carinsurance.CashAllowance);
            _sPara.Add("@MaxCashAllowanceDays", carinsurance.MaxCashAllowanceDays);
            _sPara.Add("@IsCarReplacement", carinsurance.IsCarReplacement);
            _sPara.Add("@MaxCarReplacementDays", carinsurance.MaxCarReplacementDays);
            _sPara.Add("@isPersonnalEffectsLoss", carinsurance.isPersonnalEffectsLoss);            
            _sPara.Add("@PersonnalEffectsLoss", carinsurance.PersonnalEffectLoss);
            _sPara.Add("@IsNCDProtection", carinsurance.IsNCDProtection);
            _sPara.Add("@MoreInfo", carinsurance.MoreInfo);
            _sPara.Add("@Status", carinsurance.Status);
            _sPara.Add("@Ninfo1", carinsurance.Ninfo1); //PolicyId
            _sPara.Add("@Ninfo2", carinsurance.Ninfo2); //PromotionId
            _sPara.Add("@Ninfo3", carinsurance.Ninfo3); //PolicyInfoId
       
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public bool Delete(Int64 CXCoverTypeId, Int64 ProviderCompanyId, Int64 PackageId)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocProviderCarInsuranceDeleteSingleItem]";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@CXCoverTypeId", CXCoverTypeId);
            _sPara.Add("@ProviderCompanyId", ProviderCompanyId);
            _sPara.Add("@PackageId", PackageId);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result > 0;
        }

        public CarInsurance GetCarInsuranceItem(Int32 CarInsId)
        {
            CarInsurance _carinsurance = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarInsuranceSelectSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@CarInsId", CarInsId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _carinsurance = new CarInsurance();
                    _carinsurance = FillDataRecord(_dt, 0);
                }
            }
            return _carinsurance;
        }

        public CarInsurance GetCarInsuranceItemByCover(Int64 ProviderCompanyId, Int32 CXCoverTypeId,Int64 PackageId)
        {
            CarInsurance _carinsurance = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarInsuranceSelectSingleItemByCoverId";

            SortedList _spara = new SortedList();
            _spara.Add("@ProviderCompanyId", ProviderCompanyId);
            _spara.Add("@CXCoverTypeId", CXCoverTypeId);
            _spara.Add("@PackageId", PackageId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _carinsurance = new CarInsurance();
                    _carinsurance = FillDataRecord(_dt, 0);
                }
            }
            return _carinsurance;
        }

        public DataTable GetCarInsuranceItemList(Int64 ProviderCompanyId)
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("Result");
            string _spName = "sprocCarInsuranceSelectItemList";

            SortedList _spara = new SortedList();
            _spara.Add("@ProviderCompanyId", ProviderCompanyId);

            _dtResult = _db.GetDataTableBySP(_spName, _spara);
            return _dtResult;
        }

        #region "remove for CXT"

        public DataTable CalculationEtiqaNCDProtect(Int64 CECarId, Int64 RefCEUserId, bool isNCDProtect)
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("Result");
            string _spName = "sprocProviderCarInsuranceCalculationEtiqaNCDProtect";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CECarId);
            _spara.Add("@RefCEUserId", RefCEUserId);
            _spara.Add("@isNCDProtect", isNCDProtect);

            _dtResult = _db.GetDataTableBySP(_spName, _spara);
            return _dtResult;
        }

        #endregion

        public DataTable GetsNewCalculationPerProvider(Int64 CECarId, Int64 RefCEUserId, Int64 ProviderCompanyId)
        {
            MyDatabase _db = new MyDatabase();
            DataTable _dtResult = new DataTable("Result");
            string _spName = "sprocCarInsuranceResultSelectItemListPerProvider";

            SortedList _spara = new SortedList();
            _spara.Add("@CECarId", CECarId);
            _spara.Add("@RefCEUserId", RefCEUserId);
            _spara.Add("@ProviderCompanyId", ProviderCompanyId); 

            _dtResult = _db.GetDataTableBySP(_spName, _spara);
            return _dtResult;
        }



        private CarInsurance FillDataRecord(DataTable myDataTable, int RowId)
        {
            CarInsurance myCarInsurance = new CarInsurance();
            myCarInsurance.CarInsId = Convert.ToInt32(myDataTable.Rows[RowId]["CarInsId"].ToString());
            myCarInsurance.CIVariableId = Convert.ToInt64(myDataTable.Rows[RowId]["CIVariableId"].ToString());
            myCarInsurance.ProviderCompanyId = Convert.ToInt64(myDataTable.Rows[RowId]["ProviderCompanyId"].ToString());
            myCarInsurance.CXCoverTypeId = Convert.ToInt32(myDataTable.Rows[RowId]["CXCoverTypeId"].ToString());
            myCarInsurance.PackageId = Convert.ToInt32(myDataTable.Rows[RowId]["PackageId"].ToString());
            myCarInsurance.PolicyName = myDataTable.Rows[RowId]["PolicyName"].ToString();
            myCarInsurance.TypeofPlan = myDataTable.Rows[RowId]["TypeofPlan"].ToString();

            myCarInsurance.CarAge = Convert.ToInt16(myDataTable.Rows[RowId]["CarAge"].ToString());
            myCarInsurance.SumInsured = Convert.ToDecimal (myDataTable.Rows[RowId]["SumInsured"].ToString());
            myCarInsurance.ThirdPartyBodilyPerPerson = Convert.ToDecimal(myDataTable.Rows[RowId]["ThirdPartyBodilyPerPerson"].ToString());
            myCarInsurance.ThirdPartyBodilyPerAccident = Convert.ToDecimal(myDataTable.Rows[RowId]["ThirdPartyBodilyPerAccident"].ToString());
            myCarInsurance.ThirdPartyPropertyDamage = Convert.ToDecimal(myDataTable.Rows[RowId]["ThirdPartyPropertyDamage"].ToString());
            myCarInsurance.FireAndTheft = Convert.ToDecimal(myDataTable.Rows[RowId]["FireAndTheft"].ToString());
            myCarInsurance.BailBond = Convert.ToDecimal(myDataTable.Rows[RowId]["BailBond"].ToString());

            myCarInsurance.ProposerExcess = float.Parse(myDataTable.Rows[RowId]["ProposerExcess"].ToString());
            myCarInsurance.AnnualPremium = float.Parse(myDataTable.Rows[RowId]["AnnualPremium"].ToString());
            myCarInsurance.isPersonalAccident = Convert.ToBoolean(myDataTable.Rows[RowId]["isPersonalAccident"].ToString());

            myCarInsurance.PolicyHolderAmt = float.Parse(myDataTable.Rows[RowId]["PolicyHolderAmt"].ToString());
            myCarInsurance.EachPassengerAmt = float.Parse(myDataTable.Rows[RowId]["EachPassengerAmt"].ToString());
            myCarInsurance.PersonalAccAccumulated = float.Parse(myDataTable.Rows[RowId]["PersonalAccAccumulated"].ToString());
            myCarInsurance.isMedicalExpense = Convert.ToBoolean(myDataTable.Rows[RowId]["isMedicalExpense"].ToString());
            
            myCarInsurance.MedicalExpense = float.Parse(myDataTable.Rows[RowId]["MedicalExpense"].ToString());
            myCarInsurance.isCashAllowance = Convert.ToBoolean(myDataTable.Rows[RowId]["isCashAllowance"].ToString());

            myCarInsurance.CashAllowance = float.Parse(myDataTable.Rows[RowId]["CashAllowance"].ToString());
            myCarInsurance.MaxCashAllowanceDays = Int32.Parse(myDataTable.Rows[RowId]["MaxCashAllowanceDays"].ToString());
            myCarInsurance.IsCarReplacement = Convert.ToBoolean(myDataTable.Rows[RowId]["IsCarReplacement"].ToString());
            myCarInsurance.MaxCarReplacementDays = Int32.Parse(myDataTable.Rows[RowId]["MaxCarReplacementDays"].ToString());
            myCarInsurance.isPersonnalEffectsLoss = Convert.ToBoolean(myDataTable.Rows[RowId]["isPersonnalEffectsLoss"].ToString());
            myCarInsurance.PersonnalEffectLoss = float.Parse(myDataTable.Rows[RowId]["PersonnalEffectsLoss"].ToString());
            
            myCarInsurance.IsNCDProtection = Convert.ToBoolean(myDataTable.Rows[RowId]["IsNCDProtection"].ToString());
            myCarInsurance.MoreInfo = myDataTable.Rows[RowId]["MoreInfo"].ToString();
            myCarInsurance.Status = Convert.ToBoolean(myDataTable.Rows[RowId]["Status"].ToString());

            if (myDataTable.Rows[RowId]["Ninfo1"].ToString() != "")
                myCarInsurance.Ninfo1 = Convert.ToInt64(myDataTable.Rows[RowId]["Ninfo1"].ToString());
            else
                myCarInsurance.Ninfo1 = -1;

            if (myDataTable.Rows[RowId]["Ninfo2"].ToString() != "")
                myCarInsurance.Ninfo2 = Convert.ToInt64(myDataTable.Rows[RowId]["Ninfo2"].ToString());
            else
                myCarInsurance.Ninfo2 = -1;

            if (myDataTable.Rows[RowId]["Ninfo3"].ToString() != "")
                myCarInsurance.Ninfo3 = Convert.ToInt64(myDataTable.Rows[RowId]["Ninfo3"].ToString());
            else
                myCarInsurance.Ninfo3 = -1;

            return myCarInsurance;
        }
    }
}
